using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Collections;
using System.Collections.Generic;
using System.Data.SqlClient;

/// <summary>
/// Summary description for DALAccount
/// </summary>
public class DALAccount
{
    /// <summary>
    /// Get All accounts to display
    /// </summary>
    /// <returns>List of accounts</returns>
    public static List<Account> GetAllAccounts()
    {
        return DAL.GetDataItems<Account>(SQL.GetAllAccounts, null, null);
    }

    /// <summary>
    /// Search accounts by fullname of user
    /// </summary>
    /// <param name="fullName"> fullname to search</param>
    /// <returns>List of accounts</returns>
    public static List<Account> SearchAccountByFullName(string fullName)
    {
        return DAL.GetDataItems<Account>(SQL.SearchAccountsByFullName, new string[] { "@FullName" }, new object[] { fullName });
    }

    /// <summary>
    /// Search accounts by branch name
    /// </summary>
    /// <param name="branchName">branch name to search</param>
    /// <returns>List of accounts</returns>
    public static List<Account> SearchAccountByBranchName(string branchName)
    {
        return DAL.GetDataItems<Account>(SQL.SearchAccountsByBranchName, new string[] { "@BranchName" }, new object[] { branchName });
    }

    /// <summary>
    /// Insert a new account
    /// </summary>
    /// <param name="userName">UserName of account</param>
    /// <param name="password">Password of account</param>
    /// <param name="fullName">FullName of user</param>
    /// <param name="address">Address of user</param>
    /// <param name="email">Email of user</param>
    /// <param name="phoneNumber">Phone number of user</param>
    /// <param name="role">Role of user</param>
    /// <param name="pinCode">PinCode of branch of user</param>
    /// <returns>The number of rows affected in database</returns>
    public static int InsertAccount(string userName, string password, string fullName, string address, string email, string phoneNumber, string role, string pinCode)
    {
        SqlCommand cmd = DAL.CreateCommand();
        cmd.CommandText = SQL.InsertAccount;
        cmd.Parameters.AddWithValue("@UserName", userName);
        cmd.Parameters.AddWithValue("@Password", password);
        cmd.Parameters.AddWithValue("@FullName", fullName);
        cmd.Parameters.AddWithValue("@Address", address);
        cmd.Parameters.AddWithValue("@Email", email);
        cmd.Parameters.AddWithValue("@PhoneNumber", phoneNumber);
        cmd.Parameters.AddWithValue("@Role", role);
        cmd.Parameters.AddWithValue("@PinCode", pinCode);
        return DAL.ExecuteNonQuery(cmd);
    }

    /// <summary>
    /// Get one account from database
    /// </summary>
    /// <param name="userName">UserName of account</param>
    /// <returns>Account</returns>
    public static Account GetOneAccount(string userName)
    {
        return DAL.GetDataItem<Account>(SQL.GetOneAccount, new string[] { "@UserName" }, new object[] { userName });
    }

    /// <summary>
    /// Update Account
    /// </summary>
    /// <param name="userName">UserName of account</param>
    /// <param name="password">Password of account</param>
    /// <param name="fullName">FullName of user</param>
    /// <param name="address">Address of user</param>
    /// <param name="email">Email of user</param>
    /// <param name="phoneNumber">Phone number of user</param>
    /// <param name="pinCode">PinCode of branch of user</param>
    /// <param name="isWorking">Define whether user is working for the company or not</param>
    /// <returns>Number of rows affected in database</returns>
    public static int UpdateAccount(string userName, string password, string fullName, string address, string email, string phoneNumber, string pinCode, bool isWorking)
    {
        SqlCommand cmd = DAL.CreateCommand();
        cmd.CommandText = SQL.UpdateAccount;
        cmd.Parameters.AddWithValue("@UserName", userName);
        cmd.Parameters.AddWithValue("@Password", password);
        cmd.Parameters.AddWithValue("@FullName", fullName);
        cmd.Parameters.AddWithValue("@Address", address);
        cmd.Parameters.AddWithValue("@Email", email);
        cmd.Parameters.AddWithValue("@PhoneNumber", phoneNumber);
        cmd.Parameters.AddWithValue("@IsWorking", isWorking);
        cmd.Parameters.AddWithValue("@PinCode", pinCode);
        return DAL.ExecuteNonQuery(cmd);
    }

    /// <summary>
    /// Update profile of admin
    /// </summary>
    /// <param name="userName">UserName of admin</param>
    /// <param name="fullName">FullName of admin</param>
    /// <param name="address">Address of admin</param>
    /// <param name="email">Email of admin</param>
    /// <param name="phoneNumber">PhoneNumber of admin</param>
    /// <param name="pinCode">PinCode of branch of admin</param>
    /// <returns>Number of row affected</returns>
    public static int UpdateAdmin(string userName, string fullName, string address, string email, string phoneNumber, string pinCode)
    {
        SqlCommand cmd = DAL.CreateCommand();
        cmd.CommandText = SQL.UpdateAdmin;
        cmd.Parameters.AddWithValue("@UserName", userName);
        cmd.Parameters.AddWithValue("@FullName", fullName);
        cmd.Parameters.AddWithValue("@Address", address);
        cmd.Parameters.AddWithValue("@Email", email);
        cmd.Parameters.AddWithValue("@PhoneNumber", phoneNumber);
        cmd.Parameters.AddWithValue("@PinCode", pinCode);
        return DAL.ExecuteNonQuery(cmd);
    }

    /// <summary>
    /// Delete accounts in database
    /// </summary>
    /// <param name="userNames">Array of usernames ot delete</param>
    /// <returns>Number of rows affected</returns>
    public static int DeleteAccounts(string[] userNames)
    {
        //Build sql query:
        string query = "DELETE Accounts WHERE UserName in (";
        int i;
        for (i = 0; i < userNames.Length; i++)
        {
            if (i == 0) query += "'" + userNames[i] + "'";
            else query += ",'" + userNames[i] + "'";
        }
        query += ")";
        SqlCommand cmd = DAL.CreateCommand();
        cmd.CommandText = query;
        return DAL.ExecuteNonQuery(cmd);
    }


    /// <summary>
    /// Update employee profile
    /// </summary>
    /// <param name="userName">UserName of employee</param>
    /// <param name="fullName">FullName of employee</param>
    /// <param name="address">Address of employee</param>
    /// <param name="email">Email of employee</param>
    /// <param name="phoneNumber">PhoneNumber of employee</param>
    /// <param name="pinCode">PinCode of employee</param>
    /// <returns>Number of row affected</returns>
    public static int UpdateEmployee(string userName, string fullName, string address, string email, string phoneNumber, string pinCode)
    {
        SqlCommand cmd = DAL.CreateCommand();
        cmd.CommandText = SQL.UpdateEmployeeProfile;
        cmd.Parameters.AddWithValue("@UserName", userName);
        cmd.Parameters.AddWithValue("@FullName", fullName);
        cmd.Parameters.AddWithValue("@Address", address);
        cmd.Parameters.AddWithValue("@Email", email);
        cmd.Parameters.AddWithValue("@PhoneNumber", phoneNumber);
        cmd.Parameters.AddWithValue("@PinCode", pinCode);
        return DAL.ExecuteNonQuery(cmd);
    }

    /// <summary>
    /// Get all accounts from queue
    /// </summary>
    /// <returns>List of accounts</returns>
    public List<Account> GetAccountsFromQueue()
    {
        return DAL.GetDataItems<Account>(SQL.GetAccountsFromQueue, null, null);
    }

    /// <summary>
    /// Admin confirm profile changes of employees
    /// </summary>
    /// <param name="userNames">Array of UserNames of employees to confirm</param>
    /// <returns>Number of rows affected</returns>
    public static int ConfirmProfiles(string[] userNames)
    {
        string query = "";
        int i;
        for (i = 0; i < userNames.Length; i++)
        {
            query += "exec spConfirmProfile '" + userNames[i] + "';";
        }
        SqlCommand cmd = DAL.CreateCommand();
        cmd.CommandText = query;
        return DAL.ExecuteNonQuery(cmd);
    }


    /// <summary>
    /// Delete all accounts in AccountsQueue
    /// </summary>
    /// <returns>Number of rows affected</returns>
    public static int DeleteAccountsInQueue()
    {
        SqlCommand cmd = DAL.CreateCommand();
        cmd.CommandText = SQL.DeleteAccountsQueue;
        return DAL.ExecuteNonQuery(cmd);
    }
}
